package com.dfbz.dao;

import com.dfbz.common.Constr;
import com.dfbz.dao.base.BaseDao;
import com.dfbz.model.User;
import com.dfbz.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class UserDao extends BaseDao<User> {

    public UserDao() {
        super(User.class);
    }

    /**
     * 根据用户名关联查询用户基本信息
     * @param username 用户名
     * @return map 对象 包含头像 部门名称 真实姓名
     */
    public Map<String,Object> getBaseInfoByName(String username){
        Map<String,Object> map =null;
        String sql="select u.id as user_id,u.username,ui.real_name,d.`name` as dept_name,ui.pic\n" +
                "from user u\n" +
                "left join user_info ui on u.id =ui.user_id\n" +
                "left join dept d on u.dept_id =d.id\n" +
                "where username =?";
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            map=qr.query(sql,new MapHandler(),username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public Map<String, Object> getUserInfoById(String id) {
        Map<String,Object> map =null;
        String sql="select d.`name` as dept_name,u.*,ui.*,(select count(*) as total from user_focus where user_focus_id = ?) as total_focus\n" +
                "from user u\n" +
                "left join user_info ui on ui.user_id = u.id\n" +
                "left join dept d on d.id = u.dept_id\n" +
                "where u.id = ?\n";
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            map=qr.query(sql,new MapHandler(),id,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return map;
    }

    public List<Map<String,Object>> findUsers(String userId,int currentPage, String name) {

        String sql="select ui.user_id,ui.real_name,ui.age,ui.gender,ui.introduction,\n" +
                "(select id from user_focus where user_id = ? and user_focus_id = ui.user_id) as fid\n" +
                "from user_info ui\n" +
                "limit ?,?";
        if (null!=name){
            sql = "select ui.user_id,ui.real_name,ui.age,ui.gender,ui.introduction, " +
                    "(select id from user_focus where user_id = ? and user_focus_id = ui.user_id) as fid\n" +
                    "from user_info ui\n" +
                    "where ui.real_name like ?\n" +
                    "limit ?,?";
        }
        List<Map<String, Object>> mapList = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null==name){
                mapList = qr.query(sql, new MapListHandler(),userId,(currentPage - 1) * Constr.PAGE_SIZE, Constr.PAGE_SIZE);
            }else {
                mapList = qr.query(sql, new MapListHandler(),userId, name +"%",(currentPage - 1) * Constr.PAGE_SIZE, Constr.PAGE_SIZE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return mapList;
    }

    public long getUserTotal(String name){
        long total=0L;
        String sql="select count(*) as totalUser from user_info";
        if (null!=name){
            sql="select count(*) as totalUser from user_info where real_name like ?";
        }

        Map<String,Object> map = null;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        try {
            if (null==name){
                map = qr.query(sql, new MapHandler());
            }else {
                map = qr.query(sql, new MapHandler(),name+"%");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (null!=map){
            total=Long.parseLong( map.get("totalUser").toString());

        }
        return total;
    }
}
